import time
from sanic import Sanic
from Config.Config import DBConfig
from can import Can
import openpyxl

app = Sanic.get_app("myapp")

class CONTROLLER:


    def login(self,username,password):
        db = DBConfig.db
        cursor = db.cursor()
        if username == None or password == None:
            code = 1
        else:
            sql = "SELECT * FROM user WHERE username=%s"
            cursor.execute(sql, username)
            res = cursor.fetchone()
            print(res)

        if res == None or password != res['password']:
            code = 2
        else:
            data = {
                "id": res["id"],
                "username": res["username"],
                "area": res["area"],
                "name": res["name"],
                "town": res["town"]
            }

    def get_nat_list(self,num,page,pageSize):
        db = DBConfig.db
        cursor = db.cursor()

        sql = "SELECT  donor_name,id_card,phone,error_message  FROM "+app.config.STATION+" WHERE is_checked = 1 AND error_message = '此人是持证残疾人！'  "

        # cursor.execute(sql,(app.config.STATION,(int(page) - 1) * pageSize, pageSize))
        cursor.execute(sql)
        donors = cursor.fetchall()
        return donors
    def get_count(self,num):
        db = DBConfig.db
        cursor = db.cursor()

        sql = "SELECT '已核查' as code, COUNT(*) as num  FROM " + app.config.STATION + " WHERE is_checked = 1 UNION " \
                "SELECT  '未核查', COUNT(*)  FROM " + app.config.STATION + " WHERE is_checked =0 and donor_status != 'YJ' UNION " \
                "SELECT  '验证码错误',COUNT(*)  FROM " + app.config.STATION + " WHERE is_checked = 1 AND error_code = '7010' UNION " \
                "SELECT  '系统繁忙',COUNT(*)  FROM " + app.config.STATION + " WHERE is_checked = 1 AND error_code = '7011' UNION " \
                "SELECT  '残疾人',COUNT(*)  FROM " + app.config.STATION + " WHERE is_checked = 1 AND error_message != '系统查无此人！'"

        cursor.execute(sql)
        donors = cursor.fetchall()
        return donors

    def export_excel(self,num):
        db = DBConfig.db
        cursor = db.cursor()

        sql = "SELECT  donor_name,id_card,phone,error_message  FROM " + app.config.STATION + " WHERE is_checked = 1 AND error_message != '系统查无此人！';"

        cursor.execute(sql)
        data = cursor.fetchall()

        workbook = openpyxl.Workbook()
        sheet1 = workbook.create_sheet(index=0)

        sheet1.cell(1, 1, '姓名')
        sheet1.cell(1, 2, '证件号码')
        sheet1.cell(1, 3, '联系方式')
        sheet1.cell(1, 4, '返回信息')
        for i, row in enumerate(data):
            j = 1
            i += 1
            for key, col in row.items():
                if key == 'id':
                    continue
                sheet1.cell(i + 1, j, col)
                j += 1
        t = time.strftime('%Y%m%d',time.localtime())
        path = './web/download/table'+t+'.xlsx'
        workbook.save(path)
        return path

